package com.grom.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReader {
	private Sheet sheetA;
	private Sheet sheetB;
	private Sheet sheetC;

	public Sheet getSheetA() {
		return sheetA;
	}

	public Sheet getSheetB() {
		return sheetB;
	}

	public Sheet getSheetC() {
		return sheetC;
	}

	public ExcelReader(File file) throws IOException {
		if (file == null) {
			throw new RuntimeException("File not found!");
		}
		InputStream input = new FileInputStream(file);
		Workbook wb = new XSSFWorkbook(input);
		sheetA = wb.getSheetAt(1);
		sheetB = wb.getSheetAt(2);
		sheetC = wb.getSheetAt(3);
	}

	/**
	 * return the value of a cell.
	 * 
	 * @param cell
	 * @return
	 */
	public Object getCellContent(Cell cell) {
		FormulaEvaluator evaluator = cell.getSheet().getWorkbook()
				.getCreationHelper().createFormulaEvaluator();
		try {
			if (evaluator.evaluate(cell) == null) {
				return "";
			}
		} catch (Exception e) {
			return cell.getCellFormula();
		}
		switch (evaluator.evaluate(cell).getCellType()) {
		case Cell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case Cell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			}
			return cell.getNumericCellValue();
		case Cell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue();
		case Cell.CELL_TYPE_BLANK:
			return " ";
		default:
			return "";
		}
	}
}
